/*存储过程*/
if exists(select 0 from sysobjects where xtype = 'p' and name = 'spESNotSelfGetOffSprings')
begin
    print 'drop procedure spESNotSelfGetOffSprings'
    drop procedure spESNotSelfGetOffSprings
end
go
    print 'create procedure spESNotSelfGetOffSprings'
go
create procedure spESNotSelfGetOffSprings
(
    @TableName      nvarchar(128),--数据表名称。
    @FieldValue     nvarchar(64)--项目值。
)
as
begin
    --结果表。
    declare @result table(
                            FieldID         nvarchar(32),
                            FieldName       nvarchar(64),
                            FullFieldName   nvarchar(1024),
                            ParentFieldID   nvarchar(32)
                        )
   ------------------------------------------------------------
    --tblESCatalog
    if(@TableName = 'tblESCatalog')
    begin
        if(isnull(@FieldValue,'') <> '')
        begin
            insert into @result(FieldID,FieldName,FullFieldName,ParentFieldID)
            select a.CatalogID,a.CatalogName,b.FullName,a.ParentCatalogID
            from tblESCatalog a
            left outer join fnESGetOffSprings(@TableName,null,1,'>') b
            on b.FieldID = a.CatalogID
            where a.CatalogID not in (select FieldID from fnESGetOffSprings(@TableName,@FieldValue,1,'>'))
        end else begin
            insert into @result(FieldID,FieldName,FullFieldName,ParentFieldID)
            select a.CatalogID,a.CatalogName,b.FullName,a.ParentCatalogID
            from tblESCatalog a
            left outer join fnESGetOffSprings(@TableName,null,1,'>') b
            on b.FieldID = a.CatalogID
        end
    end
    --tblESTeachingSections
     if(@TableName = 'tblESTeachingSections')
     begin
        if(isnull(@FieldValue,'') <> '')
        begin
            insert into @result(FieldID,FieldName,FullFieldName,ParentFieldID)
            select a.SectionID,a.SectionName,b.FullName,a.ParentSectionID
            from tblESTeachingSections a
            left outer join fnESGetOffSprings(@TableName,null,1,'>') b
            on b.FieldID = a.SectionID
            where a.SectionID not in (select FieldID from fnESGetOffSprings(@TableName,@FieldValue,1,'>'))
        end else begin
            insert into @result(FieldID,FieldName,FullFieldName,ParentFieldID)
            select a.SectionID,a.SectionName,b.FullName,a.ParentSectionID
            from tblESTeachingSections a
            left outer join fnESGetOffSprings(@TableName,null,1,'>') b
            on b.FieldID = a.SectionID
        end
     end
   --输出数据。
    select FieldID,FieldName,FullFieldName,ParentFieldID from @result
end
go
--------------------------------------------------------------------------------------------------------------------------------------
--spESTeachingSectionsToBind
if exists(select 0 from sysobjects where xtype = 'p' and name = 'spESTeachingSectionsToBind')
begin
    print 'drop procedure spESTeachingSectionsToBind'
    drop procedure spESTeachingSectionsToBind
end
go
    print 'create procedure spESTeachingSectionsToBind'
go
create procedure spESTeachingSectionsToBind
(
    @catalogID  nvarchar(128)
)
as
begin
    select data.SectionID,data.SectionName,b.FullName
    from tblESTeachingSections data
    left outer join dbo.fnESGetOffSprings('tblESTeachingSections',null,0,'>') b
    on b.FieldID = data.SectionID
    where data.CatalogID in (select t.FieldID from dbo.fnESGetOffSprings('tblESCatalog',@catalogID,0,'>') t)
    order by data.SectionName
end
go
--------------------------------------------------------------------------------------------------------------------------------------
